package com.action.proceso6;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.bean.proceso6.OrdenDeCompraItemOrden;
import com.conexion.DbConnection;
import com.mysql.jdbc.Connection;

public class OrdenDeCompraItemOrdenAction {

	private final String TABLA = "ordendecompra_itemorden";

	// metodo para insertar OrdenDeCompraItemOrdenAction

	public int insertar(OrdenDeCompraItemOrden obj) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbConnection.getConnection();

			String sql = "INSERT INTO " + TABLA + " VALUES(?, ?)";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, obj.getOrdendecompra_id());
			pstm.setString(2, obj.getItemorden_id());

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeCompraItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public int insertar(ArrayList<OrdenDeCompraItemOrden> lista) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbConnection.getConnection();

			for (OrdenDeCompraItemOrden obj : lista) {
				String sql = "INSERT INTO " + TABLA + " VALUES(?, ?)";
				pstm = conn.prepareStatement(sql);
				pstm.setString(1, obj.getOrdendecompra_id());
				pstm.setString(2, obj.getItemorden_id());

				pstm.executeUpdate();
			}
			return 1;

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeCompraItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public int actualizar(ArrayList<OrdenDeCompraItemOrden> lista) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbConnection.getConnection();
			String sql = "Delete from " + TABLA + " where ordendecompra_id='"
					+ lista.get(0).getOrdendecompra_id()
					+ " and itemorden_id not in ('";
			for (int i = 0; i < lista.size(); i++) {
				if (i < lista.size() - 1)
					sql += lista.get(i).getItemorden_id() + "', ";
				else
					sql += lista.get(i).getItemorden_id() + "')";
			}
			pstm = conn.prepareStatement(sql);
			pstm.executeUpdate();

			sql = "Insert into " + TABLA + " values";
			for (int i = 0; i < lista.size(); i++) {
				if (i < lista.size() - 1)
					sql += "('" + lista.get(i).getOrdendecompra_id() + "', '"
							+ lista.get(i).getItemorden_id() + "'), ";
				else
					sql += "('" + lista.get(i).getOrdendecompra_id() + "', '"
							+ lista.get(i).getItemorden_id() + "')";
			}
			sql += " where itemorden_id not in (Select itemorden_id from "
					+ TABLA + ")";
			pstm = conn.prepareStatement(sql);
			pstm.executeUpdate();

			return 1;

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeCompraItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	// meto para listar orden de compra item

	public ArrayList<OrdenDeCompraItemOrden> listar() {

		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<OrdenDeCompraItemOrden> lista = new ArrayList<OrdenDeCompraItemOrden>();

		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA;
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new OrdenDeCompraItemOrden(rs.getString(1), rs
						.getString(2)));
			}

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeCompraItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

	// metodo para buscar el Orden Compra Item

	public OrdenDeCompraItemOrden buscar(String ordendecompra_id,
			String itemorden_id) {

		Connection conn = null;
		PreparedStatement pstm = null;
		OrdenDeCompraItemOrden oci = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "Select * FROM" + TABLA
					+ " where ordendecompra_id=? and itemorden_id=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, ordendecompra_id);
			pstm.setString(2, itemorden_id);

			ResultSet rs = pstm.executeQuery();

			if (rs.next())
				oci = new OrdenDeCompraItemOrden(rs.getString(1),
						rs.getString(2));

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeCompraItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return oci;
	}

	// metodo para eliminar el orden compra item

	public int eliminar(String ordendecompra_id, String itemorden_id) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "delete from ordendecompra_itemorden where ordendecompra_id=? and itemorden_id=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, ordendecompra_id);
			pstm.setString(2, itemorden_id);

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeCompraItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public ArrayList<OrdenDeCompraItemOrden> listar(String codigo) {
		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<OrdenDeCompraItemOrden> lista = new ArrayList<OrdenDeCompraItemOrden>();

		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA + " where ordendecompra_id=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codigo);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new OrdenDeCompraItemOrden(rs.getString(1), rs
						.getString(2)));
			}

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeCompraItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

	public int eliminar(String ordendecompra_id) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "delete from ordendecompra_itemorden where ordendecompra_id=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, ordendecompra_id);

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(OrdenDeCompraItemOrdenAction.class.getName()).log(
					Level.SEVERE, null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

}
